# -*- coding: utf-8 -*-


"""
用户名    密码    姓名
归口部门    办公电话(固定电话和手机)
常用的学校邮箱   高校党(校)办固定电话
"""

# 初始化
from app import db, login_manager
from flask_login import UserMixin
import pandas as pd
import numpy as np
from itsdangerous import TimedJSONWebSignatureSerializer as Serializer, BadSignature, SignatureExpired
from flask import jsonify, current_app, g, request
from functools import wraps
from werkzeug.security import generate_password_hash, check_password_hash
import os

class Role(db.Model):
    __tablename__ = 'roles' # 角色表
    id = db.Column(db.Integer, primary_key=True) # 角色的id
    name = db.Column(db.String(20), unique=True) # 角色名称
    permissionId = db.Column(db.Integer, unique=True) # 角色的权限
    # 关系引用
    users = db.relationship('User', backref='role')

    def __repr__(self):
        return 'Role: %s' % self.name

class User(db.Model, UserMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True) # 用户的id
    username = db.Column(db.String(20), unique=True) # 用户名, 唯一
    name = db.Column(db.String(20)) # 姓名
    password = db.Column(db.String(20)) # 密码
    is_active = db.Column(db.String(1)) # 账号是否可用
    # password_hash = db.Column(db.String(128)) # 密码哈希值
    department = db.Column(db.String(20)) # 归口部门
    telephone_num = db.Column(db.String(20)) # 固定电话
    mobile_phone_num = db.Column(db.String(20), unique=True) # 手机号码
    email = db.Column(db.String(32), unique=True) # 邮箱
    office_telephone = db.Column(db.String(32)) # 校办电话
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))# 角色id
    subjects = db.relationship('Subject', backref='user') # 一个用户可以有多个学科
    points = db.relationship('Point', backref='user') # 一个用户可以有多个point

    def get_id(self):
        ## 获取用户id
        return self.id

    @login_manager.user_loader
    def user_loader(user_id):
        # return User.query.get(user_id)
        return User.query.filter_by(id=user_id).first()
    # @login_manager.request_loader
    # def request_loader(request):
    #     user_id = request.form.get('user_id')
    #     user = User.query.get(user_id)
    #     user.is_authenticated =
    # @property
    # def password(self):
    #     return AttributeError("password is not a readble attribute")
    #
    # @password.setter
    # def password(self, password):
    #     """生成hash密码"""
    #     self.password_hash = generate_password_hash(password)
    #
    #
    # def verify_password(self, password):
    #     # if self.password_hash
    #     return check_password_hash(self.password_hash, password)
    #
    # def generate_user_token(self, expiration=434200):
    #     """
    #     生成确认身份的token
    #     :param expiration: 有效期限，单位为秒，此处默认设置为12h
    #     :return: 加密后的token
    #     """
    #     s = Serializer(current_app.config['SECRET_KEY'], expires_in=expiration)
    #     return s.dumps({'user_id': self.id,
    #                     'username': self.username,
    #                     'role_id': self.role_id,
    #                     }).decode('utf-8')
    #
    # @staticmethod
    # def verify_user_token(token):
    #     s = Serializer(current_app.config['SECRET_KEY'])
    #     try:
    #         data = s.loads(token)
    #     except SignatureExpired:
    #         return None # valid token， but expired
    #     except BadSignature:
    #         return None
    #     user = User.query.get(data['user_id'])
    #     return user
    #
    # def to_json(self):
    #     """返回用户信息"""
    #     return {
    #         "user_id": self.id,
    #         "username": self.username,
    #
    #     }

    def __repr__(self):
        return 'User: %s' % self.username

class File(db.Model): # 上传的文件表
    __tablename__ = 'files'
    id = db.Column(db.Integer, primary_key=True) # 文件id
    point = db.Column(db.String(7), db.ForeignKey('points.id'))
    name = db.Column(db.String(200)) # 文件名，学科指标可以传多次
    path = db.Column(db.String(100), unique=True) # 文件路径
    file_type = db.Column(db.String(20)) # 文件类型（大学，或者学科）
    status = db.Column(db.String(20)) # 未上传，已上传，已退回
    user_id = db.Column(db.Integer, db.ForeignKey('users.id')) # 上传的用户名
    upload_time = db.Column(db.DateTime) # 上传时间

class Record(db.Model): # 上传记录表
    __tablename__ = 'record' # 用于记录用户上传历史行为, 以及撤回历史行为
    id = db.Column(db.Integer, primary_key=True) # 记录id
    point_id = db.Column(db.String(7), db.ForeignKey('points.id'))
    file_name = db.Column(db.String(200)) # 上传的文件名
    record_type = db.Column(db.String(20))  # 大学，哲学，数学等。
    user_id = db.Column(db.Integer, db.ForeignKey('users.id')) # 上传的作者
    upload_time = db.Column(db.DateTime) # 上传时间
    op_type = db.Column(db.String(20)) # 操作类型 (上传，被撤回)

class Message(db.Model):
    __tablename__ = 'messages' # 用于记录退回的消息
    id = db.Column(db.Integer, primary_key=True) # 消息的id
    point = db.Column(db.String(100)) # 消息的监测点
    withdraw_time = db.Column(db.DateTime) # 撤回时间
    reason = db.Column(db.String(200)) # 撤回原因
    user_id = db.Column(db.Integer, db.ForeignKey('users.id')) # 撤回给哪个用户


class Subject(db.Model): # 学科表
    __tablename__ = 'subjects'
    id = db.Column(db.Integer, primary_key=True) # 学科的id
    code = db.Column(db.Integer) # 学科代码
    name = db.Column(db.String(20), unique=True) # 学科名
    user_id = db.Column(db.Integer, db.ForeignKey('users.id')) # 该学科给哪个用户

class Point(db.Model):
    __tablename__ = 'points' # 监测点
    id = db.Column(db.String(7), primary_key=True) # D000101
    project = db.Column(db.String(50)) # 监测项目
    factor = db.Column(db.String(50)) # 监测要素
    point = db.Column(db.String(100)) # 监测点
    content = db.Column(db.String(600)) # 监测点说明
    explain = db.Column(db.String(600)) # 说明
    point_type = db.Column(db.String(1)) # 数据类型，D或者X
    user_id = db.Column(db.Integer, db.ForeignKey('users.id')) # 数据单位

def creat_tables():
    db.drop_all()
    db.create_all()

# 将所有的指标点存储到数据库
def insert_points(path):
    files = []
    for item in os.listdir(path):
        if not item.startswith('~$') and not item.endswith('bak.xlsx'):
            print(os.path.join(path, item))
            files.append(os.path.join(path, item))
    data = []
    if 'university' in path:
        point_type = 'D'
    elif 'subject' in path:
        point_type = 'X'
    else:
        point_type = ''
    for file in files:
        df = pd.read_excel(file, sheet_name=0)
        project = df['监测项目'].dropna(how='any').tolist()[0]
        factors = df['监测要素'].dropna(how='any').tolist()
        points = df['核心监测点'].dropna(how='any').tolist()
        point2factor = {}
        for point in points:
            for factor in factors:
                if point.startswith(factor[:5]):
                    point2factor[point] = factor
                    break
        contents = df['监测点说明'].fillna(value='')
        explains =  df['说明'].fillna(value='')
        temp = []
        for index, point in enumerate(points):
            p = Point(id= point[:7],
                      project=project,
                      factor=point2factor[point],
                      point=point,
                      content=contents[index],
                      explain=explains[index],
                      point_type=point_type)
            temp.append(p)
        db.session.add_all(temp)
        db.session.commit()

if __name__ == '__main__': # 创建表
    creat_tables() # 创建所有的数据库表

    #######     插入角色信息          #######
    role_1 = Role(name='学校管理员', permissionId=1)
    role_2 = Role(name='一流大学填报', permissionId=2)
    role_3 = Role(name='一流学科填报', permissionId=3)
    db.session.add_all([role_1, role_2, role_3])
    db.session.commit()

    ######   插入用户信息   ######
    user_1 = User(username='zhao', password='zhao123', name='赵一', is_active='Y', role_id=role_1.id)
    user_2 = User(username='qian', password='qian123', name='钱二', is_active='Y', role_id=role_2.id)
    user_3 = User(username='sun', password='sun123', name='孙三', is_active='Y', role_id=role_2.id)
    user_4 = User(username='li', password='li123', name='李四', is_active='Y', role_id=role_3.id)
    user_5 = User(username='zhou', password='zhou123', name='周五', is_active='Y', role_id=role_3.id)
    user_6 = User(username='wu', password='wu123', name='吴六', is_active='Y', role_id=role_3.id)
    db.session.add_all([user_1, user_2, user_3, user_4, user_5, user_6])
    db.session.commit()

    # 查看学习信息：学科名称，学科代码
    df = pd.read_csv('subject.txt', sep=',')
    print(df.columns)
    # 创建学科的目录
    def mkdir_subject(base_dir, subject_df):
        subjects = subject_df['学科'].tolist()
        for sub in subjects:
            os.mkdir(os.path.join(base_dir, sub))
    base_dir = 'data/fill_data/subject/'
    # mkdir_subject(base_dir, df)

    # 插入学科数据到数据库：学科代码，学科名称，负责人的ID
    df = pd.read_csv('subject.txt', sep=',')
    print(df.columns)
    a = df['代码'].tolist()
    b = df['学科'].tolist()
    temp = []
    for index, code in enumerate(a):
        user_id = np.random.randint(1, 7, 1)[0]
        s = Subject(code=code, name=b[index], user_id=user_id)
        temp.append(s)
    db.session.add_all(temp)
    db.session.commit()

    # 将大学指标检测点 和 学科指标监测点插入数据库
    insert_points('./data/template/university/')  # 插入大学监测点
    insert_points('./data/template/subject/')  # 插入学科监测点




